package com.maben.controller;

import cn.afterturn.easypoi.csv.CsvExportUtil;
import cn.afterturn.easypoi.csv.entity.CsvExportParams;
import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.deepoove.poi.XWPFTemplate;
import com.deepoove.poi.config.Configure;
import com.deepoove.poi.data.PictureType;
import com.deepoove.poi.data.Pictures;
import com.deepoove.poi.plugin.table.LoopRowTableRenderPolicy;
import com.maben.controller.vo.UserVo;
import com.maben.pojo.Resource;
import com.maben.pojo.User;
import com.maben.pojo.User2;
import com.maben.service.IResourceService;
import com.maben.service.IUser2Service;
import com.maben.service.IUserService;
import com.maben.utils.EntityUtils;
import com.maben.utils.ExcelExportEngineUtil;
import com.maben.utils.ExcelParse;
import com.opencsv.CSVReader;
import com.opencsv.CSVWriter;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.joda.time.DateTime;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private IUserService iUserService;
    @Autowired
    private IUser2Service iUser2Service;
    @Autowired
    private IResourceService iResourceService;

    @GetMapping("/findPage")
    public List<User> findPage(
            @RequestParam(value = "page", defaultValue = "1") Integer pageNo,
            @RequestParam(value = "rows", defaultValue = "10") Integer pageSize) {
        Page<User> page = new Page<>(pageNo, pageSize);
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        IPage<User> pageList = iUserService.page(page, queryWrapper);
        return pageList.getRecords();
    }

    /**
     * 使用JXL导出Excel
     */
    @GetMapping("downLoadXlsByJxl")
    public void downLoadXlsByJxl(HttpServletResponse response) {
        ServletOutputStream outputStream = null;
        WritableWorkbook workbook = null;
        try {
            outputStream = response.getOutputStream();
            //1.创建一个工作簿
            workbook = Workbook.createWorkbook(outputStream);
            //2.创建工作表
            final WritableSheet sheet1 = workbook.createSheet("sheet1", 0);
            //2.1 调整列宽
            sheet1.setColumnView(4, 25);//第一个参数：列索引；第二个参数：标准字母的宽度
            //3.添加标题
            final List<String> titles = Arrays.asList("编号", "姓名", "手机号", "入职日期", "现住址");
            Label label = null;
            for (int i = 0; i < titles.size(); i++) {
                label = new Label(i, 0, titles.get(i));
                sheet1.addCell(label);
            }
            //4.获取动态数据，放入单元格中
            final List<User> users = iUserService.list();
            int rowIndex = 1;
            for (User user : users) {
                sheet1.addCell(new Label(0, rowIndex, user.getId().toString()));
                sheet1.addCell(new Label(1, rowIndex, user.getUserName()));
                sheet1.addCell(new Label(2, rowIndex, user.getPhone()));
                sheet1.addCell(new Label(3, rowIndex, new DateTime(user.getHireDate()).toString("yyyy-MM-dd HH:mm:ss")));
                sheet1.addCell(new Label(4, rowIndex, user.getAddress()));
                rowIndex++;
            }
            //5.文件导出  一个流两个头
            final String fileName = "JXL_Excel.xls";
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException | WriteException e) {
                    e.printStackTrace();
                }
            }
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 使用POI创建xls版本的Excel
     */
    @GetMapping("downLoadXlsByPoi")
    public void downLoadXlsByPoi(HttpServletResponse response) {
        org.apache.poi.ss.usermodel.Workbook workbook = null;
        OutputStream outputStream = null;
        try {
            //1.创建一个工作簿
            workbook = new HSSFWorkbook();

            //2.创建一个工作表
            final Sheet sheet1 = workbook.createSheet("sheet1");
            //3.添加标题
            final Row titleRow = sheet1.createRow(0);
            final List<String> titles = Arrays.asList("编号", "姓名", "手机号", "入职日期", "现住址");
            for (int i = 0; i < titles.size(); i++) {
                final Cell cell = titleRow.createCell(i);
                cell.setCellValue(titles.get(i));

            }
            //4.添加动态数据
            final List<User> users = iUserService.list();
            int rowIndex = 1;
            for (User user : users) {
                final Row row = sheet1.createRow(rowIndex);
                final Cell idCell = row.createCell(0);
                idCell.setCellValue(user.getId().toString());
                final Cell userNameCell = row.createCell(1);
                userNameCell.setCellValue(user.getUserName());
                final Cell phoneCell = row.createCell(2);
                phoneCell.setCellValue(user.getPhone());
                final Cell hireDateCell = row.createCell(3);
                hireDateCell.setCellValue(new DateTime(user.getHireDate()).toString("yyyy-MM-dd HH:mm:ss"));
                final Cell addressCell = row.createCell(4);
                addressCell.setCellValue(user.getAddress());
                rowIndex++;
            }
            //5.文件导出  一个流两个头
            outputStream = response.getOutputStream();
            final String fileName = "POI_HSSF_Excel.xls";
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Cache-Control", "no-cache");
            response.addHeader("Pargam", "no-cache");
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 使用POI创建xlsx版本的Excel
     */
    @GetMapping("downLoadXlsxByPoi")
    public void downLoadXlsxByPoi(HttpServletResponse response) {
        org.apache.poi.ss.usermodel.Workbook workbook = null;
        OutputStream outputStream = null;
        try {
            //1.创建一个工作簿
            workbook = new XSSFWorkbook();
            final CellStyle bigTitleCellStyle = workbook.createCellStyle();
            //设置四边框（BorderStyle.THIN：细线 ）
            bigTitleCellStyle.setBorderBottom(BorderStyle.THIN);
            bigTitleCellStyle.setBorderLeft(BorderStyle.THIN);
            bigTitleCellStyle.setBorderRight(BorderStyle.THIN);
            bigTitleCellStyle.setBorderTop(BorderStyle.THIN);

            //2.创建一个工作表
            final Sheet sheet1 = workbook.createSheet("sheet1");
            sheet1.setColumnWidth(4, 30 * 256);//第一个参数：列索引；第二个参数：一个标准字母的256分之一
            //合并单元格(public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol))
            sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
            final Row titleRow1 = sheet1.createRow(0);
            //设置居中
            bigTitleCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
            bigTitleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            //设置行高(单位是像素)
            titleRow1.setHeightInPoints(42);
            //设置字体
            final Font font = workbook.createFont();
            font.setFontName("黑体");
            font.setFontHeightInPoints((short) 18);
            bigTitleCellStyle.setFont(font);
            final Cell titleRow1Cell = titleRow1.createCell(0);
            titleRow1Cell.setCellStyle(bigTitleCellStyle);
            titleRow1Cell.setCellValue("导出结果");
            final Cell titleRow1Cell4 = titleRow1.createCell(4);
            titleRow1Cell4.setCellStyle(bigTitleCellStyle);

            //3.添加标题
            final Row titleRow2 = sheet1.createRow(1);
            final CellStyle smallTitleCellStyle = workbook.createCellStyle();
            smallTitleCellStyle.cloneStyleFrom(bigTitleCellStyle);
            final Font font2 = workbook.createFont();
            font2.setFontName("黑体");
            font2.setFontHeightInPoints((short) 14);
            smallTitleCellStyle.setFont(font2);
            final List<String> titles = Arrays.asList("编号", "姓名", "手机号", "入职日期", "现住址");
            for (int i = 0; i < titles.size(); i++) {
                final Cell cell = titleRow2.createCell(i);
                cell.setCellValue(titles.get(i));
                cell.setCellStyle(smallTitleCellStyle);
            }
            //4.添加动态数据
            final List<User> users = iUserService.list();
            int rowIndex = 2;
            final CellStyle contentCellStyle = workbook.createCellStyle();
            contentCellStyle.cloneStyleFrom(smallTitleCellStyle);
            final Font font3 = workbook.createFont();
            font3.setFontName("宋体");
            font3.setFontHeightInPoints((short) 12);
            contentCellStyle.setFont(font3);
            for (User user : users) {
                final Row row = sheet1.createRow(rowIndex);
                final Cell idCell = row.createCell(0);
                idCell.setCellValue(user.getId().toString());
                idCell.setCellStyle(contentCellStyle);
                final Cell userNameCell = row.createCell(1);
                userNameCell.setCellValue(user.getUserName());
                userNameCell.setCellStyle(contentCellStyle);
                final Cell phoneCell = row.createCell(2);
                phoneCell.setCellValue(user.getPhone());
                phoneCell.setCellStyle(contentCellStyle);
                final Cell hireDateCell = row.createCell(3);
                hireDateCell.setCellValue(new DateTime(user.getHireDate()).toString("yyyy-MM-dd HH:mm:ss"));
                hireDateCell.setCellStyle(contentCellStyle);
                final Cell addressCell = row.createCell(4);
                addressCell.setCellValue(user.getAddress());
                addressCell.setCellStyle(contentCellStyle);
                rowIndex++;
            }
            //5.文件导出  一个流两个头
            outputStream = response.getOutputStream();
            final String fileName = "POI_XSSF_Excel.xlsx";
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Cache-Control", "no-cache");
            response.addHeader("Pargam", "no-cache");
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 使用POI创建xlsx版本的Excel
     */
    @GetMapping("downLoadXlsxByPoiTemplate")
    public void downLoadXlsxByPoiTemplate(HttpServletResponse response) throws Exception {
        org.apache.poi.ss.usermodel.Workbook workbook = null;
        OutputStream outputStream = null;
        try {
            //获取模板
            final String rootPath = ResourceUtils.getURL("classpath:").getPath();//获取项目的根目录
            final File templateFile = new File(rootPath, "/excel_template/userList.xlsx");
            //创建模板工作簿
            workbook = new XSSFWorkbook(templateFile);
            final Sheet sheet1 = workbook.getSheetAt(0);
            //获取内容样式
            final Sheet sheet2 = workbook.getSheetAt(1);
            final Row row1 = sheet2.getRow(1);
            final Cell cell = row1.getCell(1);
            final CellStyle contentCellStyle = cell.getCellStyle();
            //获取动态数据
            final List<User> users = iUserService.list();
            int rowIndex = 2;
            for (User user : users) {
                final Row row = sheet1.createRow(rowIndex);
                final Cell idCell = row.createCell(0);
                idCell.setCellValue(user.getId().toString());
                idCell.setCellStyle(contentCellStyle);
                final Cell userNameCell = row.createCell(1);
                userNameCell.setCellValue(user.getUserName());
                userNameCell.setCellStyle(contentCellStyle);
                final Cell phoneCell = row.createCell(2);
                phoneCell.setCellValue(user.getPhone());
                phoneCell.setCellStyle(contentCellStyle);
                final Cell hireDateCell = row.createCell(3);
                hireDateCell.setCellValue(new DateTime(user.getHireDate()).toString("yyyy-MM-dd HH:mm:ss"));
                hireDateCell.setCellStyle(contentCellStyle);
                final Cell addressCell = row.createCell(4);
                addressCell.setCellValue(user.getAddress());
                addressCell.setCellStyle(contentCellStyle);
                rowIndex++;
            }
            //将第二个sheet页删除
            workbook.removeSheetAt(1);
            //写入数据
            outputStream = response.getOutputStream();
            final String fileName = "POI_XSSF_Excel_Template.xlsx";
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Cache-Control", "no-cache");
            response.addHeader("Pargam", "no-cache");
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 上传批量添加
     */
    @PostMapping("uploadExcel")
    public Map<String, Object> uploadExcel(HttpServletRequest request, HttpServletResponse response) {
        final Map<String, Object> result = new HashMap<>();
        result.put("code", 0);
        org.apache.poi.ss.usermodel.Workbook workbook = null;
        InputStream inputStream = null;
        try {
            final MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
            final MultipartFile file = multipartHttpServletRequest.getFile("file");
            if (file == null) {
                return result;
            }
            inputStream = file.getInputStream();
            //1.读取一个工作簿
            workbook = new XSSFWorkbook(inputStream);
            //2.读取第一个工作表
            final Sheet sheet1 = workbook.getSheetAt(0);
            //3.从第二行读取动态数据
            final int lastRowNum = sheet1.getLastRowNum();
            final List<User> users = new ArrayList<>();
            final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            for (int i = 1; i < lastRowNum; i++) {
                final Row row = sheet1.getRow(i);
                final User user = new User();
                user.setUserName(row.getCell(0).getStringCellValue());
                user.setPhone(row.getCell(1).getStringCellValue());
                user.setHireDate(format.parse(row.getCell(2).getStringCellValue()));
                user.setAddress(row.getCell(3).getStringCellValue());
                users.add(user);
            }
            iUserService.saveBatch(users);
        } catch (Exception e) {
            e.printStackTrace();
            result.put("code", -1);
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return result;
        }
    }

    /**
     * 下载用户详情
     */
    @GetMapping("download")
    public void download(HttpServletResponse response, String id) {
        org.apache.poi.ss.usermodel.Workbook workbook = null;
        OutputStream outputStream = null;
        ByteArrayOutputStream byteArrayOutputStream = null;
        try {
            //获取模板
            ClassPathResource classPathResourceTemplate = new ClassPathResource("/excel_template/userInfo.xlsx");
            //创建模板工作簿
            workbook = new XSSFWorkbook(classPathResourceTemplate.getInputStream());
            //获取工作表
            final Sheet sheet = workbook.getSheetAt(0);
            //读取数据
            final User user = iUserService.getById(id);
            //存放数据
            sheet.getRow(1).getCell(1).setCellValue(user.getUserName());
            sheet.getRow(2).getCell(1).setCellValue(user.getPhone());
            sheet.getRow(3).getCell(1).setCellValue(new DateTime(user.getBirthday()).toString("yyyy-MM-dd"));
            sheet.getRow(4).getCell(1).setCellValue(user.getSalary());
            sheet.getRow(5).getCell(1).setCellValue(new DateTime(user.getHireDate()).toString("yyyy-MM-dd"));
            sheet.getRow(6).getCell(1).setCellValue(user.getProvince());
            sheet.getRow(6).getCell(3).setCellValue(user.getProvince());
            sheet.getRow(7).getCell(1).setCellValue(user.getAddress());
            //处理头像
            byteArrayOutputStream = new ByteArrayOutputStream();
            ClassPathResource classPathResourcePhoto = new ClassPathResource(user.getPhoto());
            BufferedImage bufferedImage = ImageIO.read(classPathResourcePhoto.getInputStream());
            ImageIO.write(bufferedImage, "JPG", byteArrayOutputStream);
            final Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();//控制图片的写入
            final XSSFClientAnchor xssfClientAnchor = new XSSFClientAnchor(
                    36000,//左边向x轴偏移多少（单位：1厘米=36000）
                    36000,//上边向y轴偏移多少
                    0, //右边向x轴偏移多少
                    0, //下边向y轴偏移多少
                    2, //开始列
                    1, //开始行
                    4, //结束列(为了使图片沾满整个，需要+1)
                    5 //结束行(为了使图片沾满整个，需要+1)
            );//控制图片的位置
            drawingPatriarch.createPicture(xssfClientAnchor, workbook.addPicture(byteArrayOutputStream.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
            //利用公式计算司龄
            sheet.getRow(5).getCell(3).setCellFormula("CONCATENATE(DATEDIF(B6,TODAY(),\"Y\"),\"年\",DATEDIF(B6,TODAY(),\"YM\"),\"个月\")");
            //写入数据
            outputStream = response.getOutputStream();
            final String fileName = "员工(" + user.getUserName() + ")详情.xlsx";
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), StandardCharsets.ISO_8859_1));
            response.addHeader("Cache-Control", "no-cache");
            response.addHeader("Pargam", "no-cache");
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (byteArrayOutputStream != null) {
                try {
                    byteArrayOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 下载用户详情,使用模板引擎
     */
    @GetMapping("download2")
    public void download2(HttpServletResponse response, String id) {
        org.apache.poi.ss.usermodel.Workbook workbook = null;
        OutputStream outputStream = null;
        try {
            //获取模板
            ClassPathResource classPathResourceTemplate = new ClassPathResource("/excel_template/userInfo2.xlsx");
            //创建模板工作簿
            workbook = new XSSFWorkbook(classPathResourceTemplate.getInputStream());
            final User user = iUserService.getById(id);
            ExcelExportEngineUtil.write2Excel(user, workbook, user.getPhoto());
            //写入数据
            outputStream = response.getOutputStream();
            final String fileName = "员工(" + user.getUserName() + ")详情.xlsx";
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), StandardCharsets.ISO_8859_1));
            response.addHeader("Cache-Control", "no-cache");
            response.addHeader("Pargam", "no-cache");
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 下载用户详情,使用easyPOI模板引擎
     */
    @GetMapping("download3")
    public void download3(HttpServletResponse response, String id) {
        org.apache.poi.ss.usermodel.Workbook workbook = null;
        OutputStream outputStream = null;
        try {
            //获取模板
            ClassPathResource classPathResourceTemplate = new ClassPathResource("/excel_template/userInfo3.xlsx");
            //创建模板参数类
            final TemplateExportParams templateExportParams = new TemplateExportParams(classPathResourceTemplate.getPath(),true);//第二个参数：全sheet有效
            //获取数据
            final User user = iUserService.getById(id);
            final Map<String, Object> dataMap = EntityUtils.entityToMap(user);
            //处理图片
            ClassPathResource classPathResourcePhoto = new ClassPathResource(user.getPhoto());
            final ImageEntity imageEntity = new ImageEntity();
            imageEntity.setUrl(classPathResourcePhoto.getPath());
            imageEntity.setColspan(2);
            imageEntity.setRowspan(4);
            dataMap.put("photo",imageEntity);
            //获取工作簿
            workbook = ExcelExportUtil.exportExcel(templateExportParams, dataMap);
            //写入数据
            outputStream = response.getOutputStream();
            final String fileName = "easyPOI员工(" + user.getUserName() + ")详情.xlsx";
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), StandardCharsets.ISO_8859_1));
            response.addHeader("Cache-Control", "no-cache");
            response.addHeader("Pargam", "no-cache");
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 导出百万数据
     */
    @PostMapping("downLoadMillion")
    public void downLoadMillion(HttpServletResponse response) {
        org.apache.poi.ss.usermodel.Workbook workbook = null;
        OutputStream outputStream = null;
        try {
            //使用sax的工作簿
            workbook = new SXSSFWorkbook();
            //定义title
            final List<String> titles = Arrays.asList("编号", "姓名", "手机号", "入职日期", "现住址");
            //使用分页查询获取数据
            int pageSize = 10_0000;
            int pageNo = 1;
            do {
                final Page<User2> page = new Page<>(pageNo, pageSize);
                final QueryWrapper<User2> queryWrapper = new QueryWrapper<>();
                IPage<User2> pageList = iUser2Service.page(page, queryWrapper);
                final List<User2> users = pageList.getRecords();
                pageSize = users.size();
                if (pageSize == 0) {
                    //当总数刚好能被整除时，直接打断循环，不产生最后一个空sheet表
                    break;
                }
                //创建工作表
                final Sheet sheet = workbook.createSheet("第" + pageNo + "个工作表");
                //设置标题
                final Row rowTitle = sheet.createRow(0);
                for (int i = 0; i < titles.size(); i++) {
                    rowTitle.createCell(i).setCellValue(titles.get(i));
                }
                //写入数据
                for (int i = 0; i < pageSize; i++) {
                    final User2 user = users.get(i);
                    final Row rowData = sheet.createRow(i + 1);
                    rowData.createCell(0).setCellValue(user.getId());
                    rowData.createCell(1).setCellValue(user.getUserName());
                    rowData.createCell(2).setCellValue(user.getPhone());
                    rowData.createCell(3).setCellValue(new DateTime(user.getHireDate()).toString("yyyy-MM-dd"));
                    rowData.createCell(4).setCellValue(user.getAddress());
                }

                pageNo++;
            } while (pageSize == 10_0000);

            //写入数据
            outputStream = response.getOutputStream();
            final String fileName = "百万数据导出.xlsx";
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), StandardCharsets.ISO_8859_1));
            response.addHeader("Cache-Control", "no-cache");
            response.addHeader("Pargam", "no-cache");
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 导入百万数据
     */
    @PostMapping("importMillion")
    public void importMillion(HttpServletRequest request) throws Exception {
        final MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
        final MultipartFile file = multipartHttpServletRequest.getFile("file");
        final ExcelParse excelParse = new ExcelParse();
        excelParse.parse(file.getInputStream());
    }

    /**
     * 导出百万数据CSV文件
     */
    @GetMapping("downLoadCSV")
    public void downLoadCSV(HttpServletResponse response) {
        //注意必须先定义返回附件的类型，否则直接输出到浏览器了就
        final String fileName = "百万数据导出CSV.csv";
        response.setContentType("text/csv");
        response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), StandardCharsets.ISO_8859_1));
        try (final OutputStream outputStream = response.getOutputStream();
             final CSVWriter csvWriter = new CSVWriter(new OutputStreamWriter(outputStream, StandardCharsets.UTF_8));
        ) {
            //定义title
            final String[] titles = {"编号", "姓名", "手机号", "入职日期", "现住址"};
            csvWriter.writeNext(titles);
            //使用分页查询获取数据
            int pageSize = 10_0000;
            int pageNo = 1;
            do {
                final Page<User2> page = new Page<>(pageNo, pageSize);
                final QueryWrapper<User2> queryWrapper = new QueryWrapper<>();
                IPage<User2> pageList = iUser2Service.page(page, queryWrapper);
                final List<User2> users = pageList.getRecords();
                pageSize = users.size();
                if (pageSize == 0) {
                    //当总数刚好能被整除时，直接打断循环
                    break;
                }

                //写入数据
                for (int i = 0; i < pageSize; i++) {
                    final User2 user = users.get(i);
                    final String[] userArr = {user.getId() + "", user.getUserName(), user.getPhone(), new DateTime(user.getHireDate()).toString("yyyy-MM-dd"), user.getAddress()};
                    csvWriter.writeNext(userArr);
                }
                csvWriter.flush();
                pageNo++;
            } while (pageSize == 10_0000);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导入百万数据CSV文件
     */
    @PostMapping("importMillionCsv")
    public void importMillionCsv(HttpServletRequest request) throws Exception {
        final MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
        final MultipartFile file = multipartHttpServletRequest.getFile("file");
        final CSVReader csvReader = new CSVReader(new InputStreamReader(file.getInputStream()));
        while (true) {
            final String[] lineArr = csvReader.readNext();
            if (lineArr == null) {
                break;
            }
            System.out.println(Arrays.toString(lineArr));
        }
    }

    /**
     * 通过id查询用户信息
     */
    @GetMapping("/{id}")
    public UserVo queryUserById(@PathVariable("id") String id) {
        final User user = iUserService.getById(id);
        final UserVo userVo = new UserVo();
        BeanUtils.copyProperties(user, userVo);

        final QueryWrapper<Resource> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("user_id", id);
        final List<Resource> resourceList = iResourceService.list(queryWrapper);
        userVo.setResourceList(resourceList);
        return userVo;
    }

    /**
     * 下载合同word
     */
    @GetMapping("downloadContract")
    public void downloadContract(HttpServletResponse response, String id) {
        //获取模板
        final ClassPathResource classPathResourceTemplate = new ClassPathResource("/word_template/contract_template.docx");
        try (final OutputStream outputStream = response.getOutputStream();
             final InputStream templateInputStream = classPathResourceTemplate.getInputStream()) {
            //定义返回数据
            final String fileName = "合同word导出.docx";
            response.setContentType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");
            response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), StandardCharsets.ISO_8859_1));
            //获取操作模板类
            final XWPFDocument document = new XWPFDocument(templateInputStream);
            //获取用户信息
            final User user = iUserService.getById(id);
            final Map<String, String> replaceMap = new HashMap<>();
            replaceMap.put("userName", user.getUserName());
            replaceMap.put("hireDate", new DateTime(user.getHireDate()).toString("yyyy-MM-dd"));
            replaceMap.put("address", user.getAddress());
            //读取模板、处理文本数据
            final List<XWPFParagraph> paragraphs = document.getParagraphs();//得到所有段落
            for (XWPFParagraph paragraph : paragraphs) {
                final List<XWPFRun> runs = paragraph.getRuns();
                for (XWPFRun run : runs) {
                    final String text = run.getText(0);
                    for (String key : replaceMap.keySet()) {
                        if (text.contains(key)) {
                            run.setText(text.replace(key, replaceMap.get(key)), 0);
                        }
                    }
                }
            }
            //获取办公用品列表
            final QueryWrapper<Resource> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("user_id", id);
            final List<Resource> resourceList = iResourceService.list(queryWrapper);
            //处理表格
            final List<XWPFTable> tables = document.getTables();
            final XWPFTable table = tables.get(0);
            final XWPFTableRow row = table.getRow(0);
            for (int i = 0; i < resourceList.size(); i++) {
                final Resource resource = resourceList.get(i);
                copyRow(table, row, i + 1);
                final XWPFTableRow tableRow = table.getRow(i + 1);
                //处理普通文本
                tableRow.getCell(0).setText(resource.getName());
                tableRow.getCell(1).setText(resource.getPrice() + "");
                tableRow.getCell(2).setText(resource.getNeedReturn() ? "需要" : "不需要");
                //处理图片
                final ClassPathResource photoResource = new ClassPathResource("/static" + resource.getPhoto());
                setCellImage(tableRow.getCell(3), photoResource);
            }

            //写入输出流
            document.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 在单元格中放图片
     */
    private void setCellImage(XWPFTableCell cell, ClassPathResource photoResource) throws Exception {
        final XWPFRun run = cell.getParagraphs().get(0).createRun();
        try (final InputStream inputStream = photoResource.getInputStream()) {
            run.addPicture(
                    inputStream,//文件输入流
                    XWPFDocument.PICTURE_TYPE_JPEG,//图片类型
                    photoResource.getFilename(),//文件名字
                    Units.toEMU(100),//图片宽
                    Units.toEMU(50) //图片高
            );
        }
    }

    /**
     * 拷贝一行（带标题行的样式）
     */
    private void copyRow(XWPFTable table, XWPFTableRow sourceRow, int index) {
        final XWPFTableRow targetRow = table.insertNewTableRow(index);//新增的行没有样式
        targetRow.getCtRow().setTrPr(sourceRow.getCtRow().getTrPr());
        final List<XWPFTableCell> tableCells = sourceRow.getTableCells();
        if (CollectionUtils.isEmpty(tableCells)) {
            return;
        }
        for (XWPFTableCell sourceCell : tableCells) {
            final XWPFTableCell targetCell = targetRow.addNewTableCell();
            //复制单元格属性
            targetCell.getCTTc().setTcPr(sourceCell.getCTTc().getTcPr());
            //复制段落属性
            targetCell.getParagraphs().get(0).getCTP().setPPr(sourceCell.getParagraphs().get(0).getCTP().getPPr());
        }
    }

    /**
     * 下载合同word 使用POI-TL
     */
    @GetMapping("downloadContractPOITL")
    public void downloadContractPOITL(HttpServletResponse response, String id) {
        //获取模板
        final ClassPathResource classPathResourceTemplate = new ClassPathResource("/word_template/contract_template2.docx");
        //需要循环的变量配置
        LoopRowTableRenderPolicy policy = new LoopRowTableRenderPolicy();
        Configure config = Configure.builder().bind("tables", policy).bind("fList", policy).build();
        try (final OutputStream outputStream = response.getOutputStream();
             final InputStream templateInputStream = classPathResourceTemplate.getInputStream();
             XWPFTemplate template = XWPFTemplate.compile(templateInputStream, config)) {
            //定义返回数据
            final String fileName = "合同word导出.docx";
            response.setContentType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");
            response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), StandardCharsets.ISO_8859_1));
            //获取用户信息
            final User user = iUserService.getById(id);
            //获取办公用品列表
            final QueryWrapper<Resource> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("user_id", id);
            final List<Resource> resourceList = iResourceService.list(queryWrapper);
            final List<Map<String, Object>> tables = new ArrayList<>();
            final List<Map<String, Object>> fList = new ArrayList<>();
            tables.add(new HashMap<String, Object>() {{
                put("fList", fList);
            }});
            for (Resource resource : resourceList) {
                final Map<String, Object> map = new HashMap<>();
                map.put("name", resource.getName());
                map.put("price", resource.getPrice() + "");
                map.put("needReturn", resource.getNeedReturn() ? "需要" : "不需要");
                //处理图片
                final ClassPathResource photoResource = new ClassPathResource("/static" + resource.getPhoto());
                map.put("photo", Pictures.ofStream(photoResource.getInputStream(), PictureType.JPEG).size(100, 50).create());
                fList.add(map);
            }
            template.render(
                    new HashMap<String, Object>() {{
                        put("userName", user.getUserName());
                        put("hireDate", new DateTime(user.getHireDate()).toString("yyyy-MM-dd"));
                        put("tables", tables);
                        put("address", user.getAddress());
                    }}
            );
            template.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 使用easyPOI导出Excel
     */
    @GetMapping("downLoadWithEasyPOI")
    public void downLoadWithEasyPOI(HttpServletResponse response) {
        final ExportParams exportParams = new ExportParams("员工列表", "sheet1", ExcelType.XSSF);
        final List<User> list = iUserService.list();
        try (final OutputStream outputStream = response.getOutputStream();
             final org.apache.poi.ss.usermodel.Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, list)) {
            final String fileName = "Export_EasyPoi.xlsx";
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(),StandardCharsets.ISO_8859_1));
            response.addHeader("Cache-Control", "no-cache");
            response.addHeader("Pargam", "no-cache");
            workbook.write(outputStream);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 使用easyPOI导出Excel
     */
    @GetMapping("downLoadWithEasyPOICSV")
    public void downLoadWithEasyPOICSV(HttpServletResponse response) {
        final List<User> list = iUserService.list();
        try (final OutputStream outputStream = response.getOutputStream();) {
            final String fileName = "Export_EasyPoi.csv";
            response.setContentType("text/csv;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(),StandardCharsets.ISO_8859_1));
            response.addHeader("Cache-Control", "no-cache");
            response.addHeader("Pargam", "no-cache");



            final CsvExportParams csvExportParams = new CsvExportParams();
            csvExportParams.setExclusions(new String[]{"一寸照片"});//忽略照片项
            CsvExportUtil.exportCsv(csvExportParams,User.class,list,outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 使用easyPOI技术解析Excel
     */
    @PostMapping("uploadExcelPOI")
    public void uploadExcelPOI(HttpServletRequest request)throws Exception{
        final MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
        final MultipartFile file = multipartHttpServletRequest.getFile("file");
        //解析Excel
        final ImportParams importParams = new ImportParams();
        importParams.setNeedSave(false);//需要保存到临时目录不？
        importParams.setTitleRows(1);//Excel中有多少行大标题
        importParams.setHeadRows(1);//Excel中有多少行小标题
        final List<User> userList = ExcelImportUtil.importExcel(file.getInputStream(), User.class, importParams);
        for (User user : userList) {
            //将id置空，让它自增
            user.setId(null);
            System.out.println(user);
            iUserService.save(user);
        }
    }

}
